# _*_ coding:utf-8 _*_
# @File  : investment.py
# @Time  : 2022-02-08  14:02
# @Author: zizle

# 投资方案
import pathlib
import datetime
import pandas as pd
from fastapi import APIRouter, Query, Header, Form, UploadFile, Body, Path
from pydantic import BaseModel, ValidationError, datetime_parse, confloat, constr, validator
from db import FAConnection, utils as dbutils
from hutool import security
from category import VARIETY_NAME
from global_constant import VARIETY

from v1_all_api.all_response import AllResponse
from v1_all_api.all_utils import datetime_utils, file_utils

from logger import logger
from configs import FILE_STORAGE

from . import work_utils


work_investment_api = APIRouter()


class PlanItem(BaseModel):
    title: str
    active_date: datetime_parse.date
    expire_date: datetime_parse.date
    build_price: float
    target_price: float
    cutloss_price: float
    group_name: str
    fund: float
    category: str
    varieties: list
    apply_firm: int
    fund_rate: confloat(ge=0, le=1) = 1

    @validator('group_name')
    def check_group_name(cls, group_name):
        if group_name not in ['宏观金融', '农业产品', '化工能源', '黑色金属']:
            raise ValidationError('group_name不在允许范围内!')
        return group_name

    @validator('category')
    def check_category(cls, category):
        if category not in ['单边', '套利']:
            raise ValidationError('category不在允许范围内!')
        return category


@work_investment_api.post('/')  # 添加投资方案
async def save_investment(ruizy_token: str = Header(...), file: UploadFile = Form(...),
                          plan_doc: str = Form(...)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    try:
        plan_item = PlanItem.parse_raw(plan_doc)
    except Exception as e:
        logger.error(f'创建投资方案错误:{e}')
        return AllResponse.validate_error(msg='plan_doc参数错误!')

    # 验证价格盈亏比等数据
    if plan_item.cutloss_price - plan_item.build_price == 0 or abs(round((plan_item.target_price - plan_item.build_price) / (plan_item.cutloss_price - plan_item.build_price), 0)) < 3:
        return AllResponse.validate_error(msg='投资方案的盈亏比需大于3:1。')

    passed_variety = VARIETY.EXCLUDE_PRICE_INTERVAL
    exist = [True for v in passed_variety if v in plan_item.varieties]
    if not exist:
        if abs(round(plan_item.target_price / plan_item.build_price - 1, 2)) < 0.06:
            return AllResponse.validate_error(msg='投资方案的价格区间需为6%以上。')
    if not plan_item.varieties:
        return AllResponse.validate_error(msg='关联品种必填,创建失败!')
    varieties = ','.join(plan_item.varieties)
    # 保存文件，保存到数据库
    year_folder = datetime.datetime.today().year
    sql_folder = f'INVESTMENT/EXCHANGE_PLAN/{year_folder}/'
    file_folder = FILE_STORAGE + sql_folder
    folder, filename, suffix = file_utils.generate_unique_filename(file_folder, file.filename, 'pdf', hashed=True)
    sql_path = sql_folder + filename + '.' + suffix
    filepath = folder + filename + '.' + suffix
    target_folder = pathlib.Path(folder)
    if not target_folder.exists():
        target_folder.mkdir(parents=True)
    # 保存文件
    with open(filepath, 'wb') as fp:
        content = await file.read()
        fp.write(content)
    # 插入数据
    create_sql = 'INSERT INTO exchange_plan (active_date,expire_date,title,category,varieties,apply_firm,creator,' \
                 'group_name,filepath,fund,fund_rate,build_price,target_price,cutloss_price,is_outer,is_ruizhi) VALUES ' \
                 '(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);'
    sql_params = [plan_item.active_date, plan_item.expire_date, plan_item.title, plan_item.category, varieties,
                  plan_item.apply_firm, person['uid'], plan_item.group_name, sql_path, plan_item.fund,
                  plan_item.fund_rate, plan_item.build_price, plan_item.target_price, plan_item.cutloss_price]

    with FAConnection() as cursor:
        is_outer = 0
        # 查询用户是否有咨询证，有的is_outer=1
        cursor.execute("SELECT out_role,is_proponent FROM ruizy_user WHERE id=%s LIMIT 1;", [person['uid']])
        op_user = cursor.fetchone()
        if op_user and op_user['is_proponent'] == 1:
            is_outer = 1
        sql_params.append(is_outer)
        cursor.execute("""
            SELECT id,user_id,variety_en,expire_date FROM exchange_plan_ruizhi_config
            WHERE user_id=%s OR variety_en IN %s;
        """, [person['uid'], varieties.split(',')])
        config = cursor.fetchall()
        is_ruizhi = 0
        for c in config:
            if c['user_id'] == person['uid'] and c['expire_date'] >= datetime.date.today():
                is_ruizhi = 1
                break
        for c in config:
            if c['variety_en'] in varieties.split(',') and c['expire_date'] < datetime.date.today():
                is_ruizhi = 0
                break
        sql_params.append(is_ruizhi)
        success = cursor.execute(create_sql, sql_params)
    if success:
        return AllResponse.operate_successfully(msg='创建投资计划成功!')
    else:
        # 移除文件
        t_file = pathlib.Path(filepath)
        if t_file.exists():
            t_file.unlink()
        return AllResponse.server_error()


@work_investment_api.get('/')  # 查询投资方案
def query_investment_plan(ruizy_token: str = Header(...), start: str = Query(...), end: str = Query(...),
                          page: int = Query(1), page_size: int = Query(50), u: int = Query(0)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    if not security.operator_is_manager(person):
        return AllResponse.forbidden_error()
    start = datetime_utils.auth_datetime_string(start, f='%Y%m%d', rf='%Y-%m-%d')
    end = datetime_utils.auth_datetime_string(end, f='%Y%m%d', rf='%Y-%m-%d')
    if not all([start, end]):
        return AllResponse.validate_error(msg='format start and end date error.')
    start_date = start + '00:00:00'
    end_date = end + ' 23:59:59'
    creator_id = u if u else 0
    sql = """
        SELECT a.id,a.create_time,a.active_date,a.expire_date,a.target_price,a.cutloss_price,a.title,a.category,
            a.filepath,a.varieties,a.examine_level,a.level_time,a.examine_ret,a.examine_time,a.apply_firm,a.build_time,a.close_time,
            a.audit_flag,a.auditor,a.audit_time,a.plan_profit,
            b.admin_name 
        FROM exchange_plan As a INNER JOIN ruizy_user As b ON a.creator=b.id 
        WHERE a.is_delete=0 AND a.create_time>=%s AND a.create_time<=%s AND IF('0'=%s,TRUE,a.creator=%s) 
        ORDER BY a.create_time DESC;
    """
    db_conn = FAConnection()
    ret = db_conn.query_paginator(sql, param=[start_date, end_date, creator_id, creator_id],
                                  page=page, page_size=page_size)
    level_texts = {1: 'E', 2: 'D', 3: 'C', 4: 'B', 5: 'A'}
    ret_texts = {1: '展示', 2: '实盘', 3: '外发'}
    for item in ret['data']:
        item['create_time'] = item['create_time'].strftime('%Y-%m-%d %H:%M:%S')
        item['variety'] = ','.join([VARIETY_NAME.get(i, i) for i in item['varieties'].split(',')])
        item['firm_text'] = '是' if item['apply_firm'] else '否'
        item['level_text'] = '' if item['examine_level'] == 0 else level_texts.get(item['examine_level'], '未知')
        item['ret_text'] = '' if item['examine_ret'] == 0 else ret_texts.get(item['examine_ret'], '未知')
        item['audit_text'] = '符合' if item['audit_flag'] else '不符合'
        item['audit_time'] = item['audit_time'].strftime('%Y-%m-%d %H:%M:%S') if item['audit_time'] else '-'
        item['level_time'] = item['level_time'].strftime('%Y-%m-%d %H:%M:%S') if item['level_time'] else '-'
        item['examine_time'] = item['examine_time'].strftime('%Y-%m-%d %H:%M:%S') if item['examine_time'] else '-'
        item['state_dept'] = '待开始'
        if item['build_time']:
            item['state_dept'] = '运行中'
        if item['close_time']:
            item['state_dept'] = '已结束'
    return AllResponse.operate_successfully(data=ret)


@work_investment_api.get('/my/')  # 查询个人投资方案
def my_investment(ruizy_token: str = Header(...), page: int = Query(1, ge=1),
                  page_size: int = Query(10, ge=1), kw: str = Query(None)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    start, offset = (page - 1) * page_size, page_size
    if kw:
        query_sql = 'SELECT SQL_CALC_FOUND_ROWS id,create_time,active_date,expire_date,title,category,varieties,' \
                    'apply_firm,examine_level,level_explain,ret_explain,examine_ret,group_name,filepath,plan_profit,' \
                    'audit_flag,auditor ' \
                    'FROM exchange_plan ' \
                    'WHERE creator=%s AND is_delete=0 AND title like %s ' \
                    'ORDER BY create_time DESC LIMIT %s,%s;'
        params = [person['uid'], f'%{kw}%', start, offset]
    else:
        query_sql = 'SELECT SQL_CALC_FOUND_ROWS id,create_time,active_date,expire_date,title,category,varieties,' \
                    'apply_firm,examine_level,level_explain,ret_explain,examine_ret,group_name,filepath,plan_profit,' \
                    'audit_flag,auditor ' \
                    'FROM exchange_plan ' \
                    'WHERE creator=%s AND is_delete=0 ORDER BY create_time DESC LIMIT %s,%s;'
        params = [person['uid'], start, offset]
    db_conn = FAConnection('查询投资计划')
    query_ret = db_conn.paginator_query(sql=query_sql, page=page, page_size=page_size, param=params)
    level_texts = {1: 'E', 2: 'D', 3: 'C', 4: 'B', 5: 'A'}
    ret_texts = {1: '展示', 2: '实盘', 3: '外发'}
    for item in query_ret['records']:
        item['create_time'] = item['create_time'].strftime('%Y-%m-%d %H:%M:%S')
        item['variety'] = ','.join([VARIETY_NAME.get(i, i) for i in item['varieties'].split(',')])
        item['firm_text'] = '是' if item['apply_firm'] else '否'
        item['level_text'] = '' if item['examine_level'] == 0 else level_texts.get(item['examine_level'], '未知')
        item['ret_text'] = '' if item['examine_ret'] == 0 else ret_texts.get(item['examine_ret'], '未知')
        item['show_record'] = False
    return AllResponse.operate_successfully(data=query_ret)


@work_investment_api.delete('/my/')  # 删除个人投资方案
def delete_my_investment(ruizy_token: str = Header(...), plan_id: int = Query(..., ge=1)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    db_conn = FAConnection()
    # 查方案详情
    plan_sql = "SELECT examine_level,examine_ret,close_time FROM exchange_plan WHERE id=%s LIMIT 1;"
    plan_obj = db_conn.query(plan_sql, param=[plan_id], fetchone=True)[0]
    if not plan_obj:
        return AllResponse.validate_error(msg='目标投资方案不存在。')
    if plan_obj['close_time']:
        return AllResponse.validate_error(msg='已结束的方案不能删除了。')
    if plan_obj['examine_level'] != 0 or plan_obj['examine_ret'] != 0:
        return AllResponse.validate_error(msg='已评级或审核的方案不能删除了。')
    db_conn = FAConnection()
    # 方案标记删除
    sql = "UPDATE exchange_plan SET is_delete=1 AND creator=%s WHERE id=%s LIMIT 1;"
    prm = [person['uid'], plan_id]
    print(prm)

    _, success = db_conn.execute(sql, prm)
    return AllResponse.operate_successfully() if success else AllResponse.server_error()


class PlanOrderItem(BaseModel):
    active_date: str
    description: constr(min_length=1, max_length=512)


@work_investment_api.post('/my/investmentRecord/')  # 保存方案的跟踪记录
def create_plan_order(plan_id: int = Query(..., ge=1), order_item: PlanOrderItem = Body(...)):
    active_date = datetime_utils.auth_datetime_string(order_item.active_date)

    if not active_date:
        return AllResponse.validate_error(msg='日期时间无法格式化:`%Y-%m-%d %H:%M:%S`')

    db_conn = FAConnection(conn_name='方案交易记录')
    # 查询方案对象
    query_sql = 'SELECT id,build_time FROM exchange_plan WHERE id=%s;'
    planobj = db_conn.query(query_sql, param=[plan_id], fetchone=True, keep_conn=True)[0]
    if not planobj:
        db_conn.close()
        return AllResponse.server_no_content()

    # 创建记录
    save_sql = 'INSERT INTO exchange_plan_order (plan_id,active_date,description) VALUES (%s,%s,%s);'
    save_param = [plan_id, order_item.active_date, order_item.description]

    sql_list = [save_sql]
    param_list = [save_param]
    if not planobj['build_time']:
        # 修改建仓时间
        now_time = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        update_sql = 'UPDATE exchange_plan SET build_time=%s WHERE id=%s;'
        update_param = [now_time, plan_id]
        sql_list.append(update_sql)
        param_list.append(update_param)

    success = db_conn.execute_tasks(sql_list=sql_list, param_list=param_list)
    return AllResponse.operate_successfully() if success else AllResponse.server_error()


@work_investment_api.get('/my/investmentRecord/')  # 获取方案的跟踪记录
def get_investment_record(plan_id: int = Query(..., ge=1)):
    query_plan = 'SELECT id,plan_profit FROM exchange_plan WHERE id=%s;'

    sql = 'SELECT id,create_time,plan_id,active_date,description ' \
          'FROM exchange_plan_order WHERE plan_id=%s ORDER BY active_date DESC;'
    db_conn = FAConnection(conn_name='查询方案记录')
    plan_obj = db_conn.query(query_plan, param=[plan_id], fetchone=True, keep_conn=True)[0]
    if not plan_obj:
        db_conn.close()
        return AllResponse.server_no_content()

    records = db_conn.query(sql, param=[plan_id])
    for item in records:
        item['active_time'] = item['active_date'].strftime('%Y-%m-%d %H:%M:%S')
    response_data = {'records': records, 'plan_profit': round(plan_obj['plan_profit'], 2)}
    return AllResponse.operate_successfully(data=response_data)


@work_investment_api.get('/examine/')  # 审核处理时查询投资方案
def examine_investment(level: int = Query(-1, ge=-1), ret: int = Query(-1, ge=-1),
                       page: int = Query(1, ge=1), page_size: int = Query(1, ge=1)):
    query_sql = 'SELECT a.id,a.create_time,a.active_date,a.expire_date,a.title,a.category,a.varieties,a.apply_firm,' \
                'a.examine_level,a.level_explain,a.examine_ret,a.ret_explain,a.creator,a.group_name,a.filepath,' \
                'a.plan_profit,' \
                'u.admin_name ' \
                'FROM exchange_plan AS a ' \
                'INNER JOIN ruizy_user AS u ON a.creator=u.id ' \
                'WHERE a.is_delete=0 ' \
                'ORDER BY a.create_time DESC;'
    # 查询结果，处理数据
    db_conn = FAConnection()
    records = db_conn.query(query_sql)
    if level == 0:  # 未评级的
        records = list(filter(lambda x: x['examine_level'] == 0, records))
    if level == 1:  # 已评级的
        records = list(filter(lambda x: x['examine_level'] > 0, records))

    if ret == 0:  # 未审批的
        records = list(filter(lambda x: x['examine_ret'] == 0, records))
    if ret == 1:  # 已审批的
        records = list(filter(lambda x: x['examine_ret'] > 0, records))

    # 处理分页
    start, offset = (page - 1) * page_size, page_size
    ret_records = records[start: start + page_size]
    total_page = (len(records) + page_size - 1) // page_size

    level_texts = {1: 'E', 2: 'D', 3: 'C', 4: 'B', 5: 'A'}
    ret_texts = {1: '展示', 2: '实盘', 3: '外发'}
    for item in ret_records:
        item['create_time'] = item['create_time'].strftime('%Y-%m-%d %H:%M:%S')
        item['active_date'] = item['active_date'].strftime('%Y-%m-%d')
        item['expire_date'] = item['expire_date'].strftime('%Y-%m-%d')
        item['variety'] = ','.join([VARIETY_NAME.get(i, i) for i in item['varieties'].split(',')])
        item['firm_text'] = '是' if item['apply_firm'] else '否'
        item['level_text'] = '' if item['examine_level'] == 0 else level_texts.get(item['examine_level'], '未知')
        item['examine_level'] = item['level_text']
        item['ret_text'] = '' if item['examine_ret'] == 0 else ret_texts.get(item['examine_ret'], '未知')
        item['examine_level'] = None if not item['examine_level'] else item['examine_level']
        item['examine_ret'] = None if not item['examine_ret'] else item['examine_ret']

    response_data = {'page': page, 'page_size': page_size, 'total_page': total_page, 'total_count': len(records),
                     'plans': ret_records}
    return AllResponse.operate_successfully(data=response_data)


class AuditItem(BaseModel):
    plan_id: int
    audit_flag: int


@work_investment_api.put('/audit/')  # 审查投资方案是否符合规定
def audit_investment(ruizy_token: str = Header(...), audit_item: AuditItem = Body(...)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    # 查询用户
    db_conn = FAConnection()

    # op_user = db_conn.query("SELECT id,admin_name FROM ruizy_user WHERE id=%s LIMIT 1;", param=[person['uid']],
    #                         fetchone=True, keep_conn=True)[0]
    # if not op_user:
    #     db_conn.close()
    #     return AllResponse.validate_error(msg='用户不存在！')

    sql = "UPDATE exchange_plan SET audit_flag=%s,auditor=%s,audit_time=%s WHERE id=%s LIMIT 1;"
    _, success = db_conn.execute(sql, param=[audit_item.audit_flag, person['uid'],
                                             datetime_utils.today(f='%Y-%m-%d %H:%M:%S'), audit_item.plan_id])
    return AllResponse.operate_successfully() if success else AllResponse.server_error()


@work_investment_api.put('/examine/{plan_id}/')  # 修改方案记录，审批和结果
def modify_exchange_plan(plan_id: int = Path(..., ge=1), body_data: dict = Body(...)):
    accept_column = ['examine_level', 'level_explain', 'examine_ret', 'ret_explain', 'plan_profit']
    accept_data = dbutils.exclude_columns(body_data, accept_column)
    # 转为1-5存储
    if 'examine_level' in accept_data.keys():  # 评级时间
        if accept_data['examine_level'] not in ['A', 'B', 'C', 'D', 'E']:
            return AllResponse.validate_error(msg='程序内部错误:不支持的评级结果!')
        level_dict = {'A': 5, 'B': 4, 'C': 3, 'D': 2, 'E': 1}
        accept_data['examine_level'] = level_dict.get(accept_data['examine_level'])
        accept_data['level_time'] = datetime_utils.today(f='%Y-%m-%d %H:%M:%S')
    if 'examine_ret' in accept_data.keys():  # 批准时间
        accept_data['examine_time'] = datetime_utils.today(f='%Y-%m-%d %H:%M:%S')
    if 'plan_profit' in accept_data.keys():  # 结束时间
        accept_data['close_time'] = datetime_utils.today(f='%Y-%m-%d %H:%M:%S')

    middle_sql = dbutils.create_sql(accept_data)  # 修改的sql语句

    accept_data['id'] = plan_id
    sql = 'UPDATE exchange_plan SET ' + middle_sql + 'WHERE id=%(id)s LIMIT 1;'
    db_conn = FAConnection()
    _, success = db_conn.execute(sql, param=accept_data)
    return AllResponse.operate_successfully() if success else AllResponse.server_error()


@work_investment_api.get('/statistics/')  # 按时间统计投资方案(数量按创建时间、收益率按结束时间)
def investment_plan_statistics(ruizy_token: str = Header(...), start: str = Query(...), end: str = Query(...),
                               st: int = Query(0, ge=0, le=1)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()

    start_date = datetime_utils.auth_datetime_string(datetime_string=start, f='%Y%m%d', rf='%Y-%m-%d')
    end_date = datetime_utils.auth_datetime_string(datetime_string=end, f='%Y%m%d', rf='%Y-%m-%d')
    if not all([start_date, end_date]):
        return AllResponse.validate_error(msg='日期查询格式错误!')

    is_admin = security.operator_is_manager(person)
    only_view_team = True if st else False
    members, is_leader = work_utils.get_operator_group_members(user_id=person['uid'], query_date=start_date)

    start_date += ' 00:00:00'
    end_date += ' 23:59:59'
    # 查询创建时间在区域内的数据
    sql = """
        SELECT a.create_time,a.id,a.creator,a.build_time,a.close_time,a.fund,a.plan_profit,b.admin_name   
        FROM exchange_plan As a
        INNER JOIN ruizy_user As b ON a.creator=b.id 
        WHERE a.audit_flag=1 AND a.is_delete=0 AND a.create_time>=%s AND a.create_time<=%s;
    """
    prm = (start_date, end_date)
    db_conn = FAConnection()
    records = db_conn.query(sql, param=prm, keep_conn=True)

    # 查询结束时间在时间区域内的数据
    sql2 = """
        SELECT a.create_time,a.id,a.creator,a.build_time,a.close_time,a.fund,a.fund_rate,a.plan_profit,b.admin_name   
        FROM exchange_plan As a
        INNER JOIN ruizy_user As b ON a.creator=b.id 
        WHERE a.audit_flag=1 AND a.is_delete=0 AND a.close_time IS NOT NULL 
        AND a.close_time>=%s AND a.close_time<=%s;
    """
    prm2 = (start_date, end_date)
    finish_records = db_conn.query(sql2, param=prm2)

    # 查看的数据控制
    if is_admin:  # 管理员可以统计所有人或组员
        if only_view_team:  # 只查看组员
            members_id = [m['user_id'] for m in members]  # 组员id
            records = list(filter(lambda x: x['creator'] in members_id, records))
            finish_records = list(filter(lambda x: x['creator'] in members_id, finish_records))
    else:  # 不是管理员，只能查自己或组员
        members_id = [person['uid']]
        if only_view_team and is_leader:
            members_id = [m['user_id'] for m in members]  # 组员id
        records = list(filter(lambda x: x['creator'] in members_id, records))
        finish_records = list(filter(lambda x: x['creator'] in members_id, finish_records))

    user_dict = {}

    # 统计数量(以创建时间落在查询期间为基准)
    for plan in records:
        if plan['creator'] in user_dict.keys():
            user_data = user_dict[plan['creator']]
            user_data['total_count'] += 1
            if plan['close_time']:  # 策略结束
                user_data['finish_count'] += 1
            else:
                user_data['running_count'] += 1
        else:
            total_fund = 0
            total_profit = 0

            total_count = 1
            running_count = 1
            finish_count = 0
            if plan['close_time']:  # 策略结束
                running_count = 0
                finish_count = 1
            user_dict[plan['creator']] = {
                'user_id': plan['creator'],
                'admin_name': plan['admin_name'],
                'success_count': 0,  # 成功数，以结束时间算
                'running_count': running_count,
                'finish_count': finish_count,
                'close_finish_count': 0,
                'total_count': total_count,
                'total_fund': total_fund,
                'total_profit': total_profit
            }

    # 统计成功率、收益率(以结束时间落在查询期间为基准)
    for plan in finish_records:
        if plan['creator'] in user_dict.keys():
            user_data = user_dict[plan['creator']]
            user_data['close_finish_count'] += 1
            user_data['total_fund'] += round(plan['fund'] * plan['fund_rate'], 2)
            user_data['total_profit'] += plan['plan_profit']
            if plan['plan_profit'] > 0:
                user_data['success_count'] += 1

        else:
            running_count, success_count, total_profit = 0, 0, 0
            total_fund = round(plan['fund'] * plan['fund_rate'], 2)
            if plan['plan_profit'] > 0:
                success_count = 1
            total_profit = plan['plan_profit']
            user_dict[plan['creator']] = {
                'user_id': plan['creator'],
                'admin_name': plan['admin_name'],
                'running_count': running_count,
                'close_finish_count': 1,  # 计算成功率的
                'finish_count': 0,  # 统计时间段内以发布日期为基准没数量，却有结束量
                'total_count': 0,
                'success_count': success_count,
                'total_fund': total_fund,
                'total_profit': total_profit
            }

    statistics_data = []
    for _, v in user_dict.items():
        v['total_profit_rate'] = 0
        v['success_rate'] = 0
        # 成功率
        if v['close_finish_count'] > 0 and v['close_finish_count'] > 0:
            v['success_rate'] = round(v['success_count'] / v['close_finish_count'], 4)
        if v['total_fund'] > 0:
            v['total_profit_rate'] = round(v['total_profit'] / v['total_fund'], 4)
        statistics_data.append(v)
    statistics_data.sort(key=lambda x: x['total_count'], reverse=True)
    if len(statistics_data) > 0:
        # 合计
        t_running_count = sum([st['running_count'] for st in statistics_data])
        t_finish_count = sum([st['finish_count'] for st in statistics_data])
        t_success_count = sum([st['success_count'] for st in statistics_data])
        t_total_fund = sum([st['total_fund'] for st in statistics_data])
        t_total_profit = sum([st['total_profit'] for st in statistics_data])
        statistics_data.append({
            'user_id': -1,
            'admin_name': '合计',
            'total_count': len(records),
            'running_count': t_running_count,  # 创建日期为基准
            'success_count': t_success_count,  # 结束日期为基准的成功数
            'finish_count': t_finish_count,  # 创建日期为基准的结束数
            'close_finish_count': len(finish_records),  # 结束日期为基准总数
            'total_fund': '%.0f万' % (t_total_fund/10000),  # 结束日期为基准
            'total_profit': t_total_profit,  # 结束日期为基准的收益
            'success_rate': round(t_success_count/len(finish_records), 4) if len(finish_records) > 0 else 0,  # 结束日期为基准的成功数/总数
            'total_profit_rate': round(t_total_profit / t_total_fund, 4) if t_total_fund > 0 else 0  # 结束日期为基准的收益率
        })
    return AllResponse.operate_successfully(data=statistics_data)


@work_investment_api.get('/statisticsMonthly/')  # 按月份统计投资计划数量
def statistics_investment_monthly(ruizy_token: str = Header(...), start: str = Query(...), end: str = Query(...),
                                  st: int = Query(1, ge=1, le=2)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()

    start_date = datetime_utils.auth_datetime_string(datetime_string=start, f='%Y%m%d', rf='%Y-%m-%d')
    end_date = datetime_utils.auth_datetime_string(datetime_string=end, f='%Y%m%d', rf='%Y-%m-%d')
    if not all([start_date, end_date]):
        return AllResponse.validate_error(msg='日期查询格式错误!')

    is_admin = security.operator_is_manager(person)
    members, is_leader = work_utils.get_operator_group_members(user_id=person['uid'], query_date=start_date)
    start_date += ' 00:00:00'
    end_date += ' 23:59:59'
    db_conn = FAConnection()
    # 查询创建时间在区域内的数据
    sql = """
        SELECT a.create_time,a.id,a.creator,a.build_time,a.close_time,a.fund,a.plan_profit,b.admin_name   
        FROM exchange_plan As a
        INNER JOIN ruizy_user As b ON a.creator=b.id 
        WHERE a.audit_flag=1 AND a.is_delete=0 AND a.create_time>=%s AND a.create_time<=%s;
    """
    investment_records = db_conn.query(sql, param=[start_date, end_date])
    # 查看的数据控制
    if is_admin:  # 管理员可以统计所有人或组员
        if st == 2:  # 只查看组员
            members_id = [m['user_id'] for m in members]  # 组员id
            investment_records = list(filter(lambda x: x['creator'] in members_id, investment_records))
    else:  # 不是管理员，只能查自己或组员
        members_id = [person['uid']]
        if st == 2 and is_leader:
            members_id = [m['user_id'] for m in members]  # 组员id
        investment_records = list(filter(lambda x: x['creator'] in members_id, investment_records))

    month_list = datetime_utils.month_of_range(start=start_date, end=end_date, ft='%Y-%m-%d %H:%M:%S')
    # 对数据按人员按月份统计
    statistics_data, table_columns = work_utils.statistics_monthly(investment_records, month_list=month_list,
                                                                   running_count=True, flag=True)
    response_data = {
        'statistics_data': statistics_data,
        'table_columns': table_columns
    }
    return AllResponse.operate_successfully(data=response_data)


@work_investment_api.get('/statisticsChart/')  # 查询投资方案的收益率，成功率图形配置（时间基础为结束时间，运行中不统计）
def investment_statistics_chart(ruizy_token: str = Header(...), start: str = Query(...), end: str = Query(...),
                                user: int = Query(0, ge=0)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()

    start_date = datetime_utils.auth_datetime_string(datetime_string=start, f='%Y%m%d', rf='%Y-%m-%d')
    end_date = datetime_utils.auth_datetime_string(datetime_string=end, f='%Y%m%d', rf='%Y-%m-%d')
    if not all([start_date, end_date]):
        return AllResponse.validate_error(msg='日期查询格式错误!')
    start_date += ' 00:00:00'
    end_date += ' 23:59:59'
    user_id = 'NOUSER' if not user else user
    # 查询结束时间在时间区域内的数据
    sql2 = """
            SELECT a.create_time,a.id,a.creator,a.build_time,a.close_time,a.fund,a.plan_profit,b.admin_name   
            FROM exchange_plan As a
            INNER JOIN ruizy_user As b ON a.creator=b.id WHERE a.close_time IS NOT NULL 
            AND a.close_time>=%s AND a.close_time<=%s AND IF('NOUSER'=%s,TRUE,a.creator=%s);
        """
    prm2 = (start_date, end_date, user_id, user_id)
    db_conn = FAConnection()
    finish_records = db_conn.query(sql2, param=prm2)
    # 进行按日期的数据统计
    df = pd.DataFrame(finish_records)
    if df.empty:
        return AllResponse.operate_successfully()
    df['close_date'] = df['close_time'].apply(lambda x: x.strftime('%Y%m%d'))
    count_df = df.groupby(by='close_date', as_index=False)['id'].count()
    count_df.rename(columns={'id': 'total_count'}, inplace=True)
    success_df = df[df['plan_profit'] > 0].groupby(by='close_date', as_index=False)['id'].count()
    success_df.rename(columns={'id': 'success_count'}, inplace=True)
    # 按日期统计资金、收益的和
    profit_df = df.groupby(by='close_date', as_index=False)[['fund', 'plan_profit']].sum()
    profit_df = pd.merge(profit_df, count_df, how='outer')  # 合并总数量
    profit_df = pd.merge(profit_df, success_df, how='outer')  # 合并成功数
    profit_df.fillna(0, inplace=True)
    profit_df.sort_values(by='close_date', inplace=True)
    profit_df['fund_cum'] = profit_df['fund'].cumsum()
    profit_df['profit_cum'] = profit_df['plan_profit'].cumsum()
    profit_df['total_count_cum'] = profit_df['total_count'].cumsum()
    profit_df['success_count_cum'] = profit_df['success_count'].cumsum()
    # 当日的前端没有启用，不进行计算
    # profit_df['current_profit_rate'] = (profit_df['plan_profit'] / profit_df['fund']).round(4)  # 当日收益率
    profit_df['cum_profit_rate'] = (profit_df['profit_cum'] / profit_df['fund_cum']).round(4)  # 累计收益率
    # profit_df['current_success_rate'] = (profit_df['success_count'] / profit_df['total_count']).round(4)  # 当日成功率
    profit_df['cum_success_rate'] = (profit_df['success_count_cum'] / profit_df['total_count_cum']).round(4)  # 累计成功率
    # print(profit_df)
    return AllResponse.operate_successfully(data=profit_df.to_dict(orient='records'))


@work_investment_api.get('/tj/monthlyCount/')  # 查询年数据，按每月统计出个人方案数量[工作管理系统]
def monthly_count(year: int = Query(..., ge=2000)):
    ts, te = datetime_utils.year_start_end(year)
    query_sql = 'SELECT a.id,a.create_time,a.creator,a.close_time,a.fund,a.plan_profit,' \
                'b.admin_name ' \
                'FROM exchange_plan As a ' \
                'INNER JOIN ruizy_user AS b ON a.creator=b.id ' \
                'WHERE a.is_delete=0 AND a.create_time>=%s AND a.create_time<=%s;'
    query_prm = [ts, te]
    db_conn = FAConnection()
    df = pd.DataFrame(db_conn.query(query_sql, query_prm))
    if df.empty or df[pd.notnull(df['close_time'])].empty:
        return AllResponse.server_no_content()
    df['month'] = df['create_time'].apply(lambda x: x.strftime('%Y-%m'))
    df = df[pd.notnull(df['close_time'])]
    # 获取成功的计划
    sus_df = df[df['plan_profit'].gt(0)]
    # 人员按月分组计数
    count_df = df.groupby(by=['month', 'creator', 'admin_name'])['admin_name'].count()
    sus_count_df = sus_df.groupby(by=['month', 'creator', 'admin_name'])['admin_name'].count()
    sus_count_dict = sus_count_df.to_dict()
    cache_data = []
    for k, c in count_df.to_dict().items():
        # print(k[0], k[2], '总数:', c, '成功数:', sus_count_dict.get(k, 0))
        cache_data.append({
            'author': k[2],
            'month': k[0],
            'count': c,
            'success_count': sus_count_dict.get(k, 0),
        })
    author_monthly_count = {}
    for item in cache_data:
        if item['author'] in author_monthly_count.keys():
            author_monthly_count[item['author']][item['month']] = item['count']
        else:
            author_monthly_count[item['author']] = {item['month']: item['count']}
    response_data = []
    for k, v in author_monthly_count.items():
        v['year_total'] = sum([c for _, c in v.items()])
        v['username'] = k
        response_data.append(v)
    # print(df)
    # print(sus_df)
    # for i in response_data:
    #     print(i)
    return AllResponse.operate_successfully(data=response_data)


@work_investment_api.get('/tj/count/')  # 按日期范围查询数量和成功率[工作管理系统]
def strategy_count(year: int = Query(..., ge=2000), month: int = Query(None, ge=1, le=12)):
    if month:
        ts, te = datetime_utils.month_start_end(year, month)
    else:
        ts, te = datetime_utils.year_start_end(year)
    query_sql = 'SELECT a.id,a.create_time,a.creator,a.close_time,a.fund,a.plan_profit,a.examine_level,' \
                'b.admin_name ' \
                'FROM exchange_plan As a ' \
                'INNER JOIN ruizy_user AS b ON a.creator=b.id ' \
                'WHERE a.is_delete=0 AND a.create_time>=%s AND a.create_time<=%s;'
    query_prm = [ts, te]
    db_conn = FAConnection()
    df = pd.DataFrame(db_conn.query(query_sql, query_prm))
    if df.empty or df[pd.notnull(df['close_time'])].empty:
        return AllResponse.server_no_content(msg='没有数据...')
    # 去掉未结束的方案
    df = df[pd.notnull(df['close_time'])]
    # 获取成功的方案
    sus_df = df[df['plan_profit'].gt(0)]
    # 人员分组计数
    count_df = df.groupby(by=['creator', 'admin_name'])['admin_name'].count()
    sus_count_df = sus_df.groupby(by=['creator', 'admin_name'])['admin_name'].count()
    sus_count_dict = sus_count_df.to_dict()
    # 人员分组计算收益和资金情况
    fund_df = df.groupby(by=['creator', 'admin_name'], as_index=False)[['plan_profit', 'fund', 'examine_level']].sum()
    fund_df['plan_profit'] = fund_df['plan_profit'].astype(float)
    fund_df['fund'] = fund_df['fund'].astype(float)
    fund_dict = {i['admin_name']: (i['plan_profit'], i['fund'], i['examine_level']) for i in fund_df.to_dict(orient='records')}
    response_data = []
    for k, c in count_df.to_dict().items():
        response_data.append({
            'author': k[1],
            'count': c,
            'success_count': sus_count_dict.get(k, 0),
            'profit': fund_dict.get(k[1])[0],
            'fund': fund_dict.get(k[1])[1],
            'level_score': fund_dict.get(k[1])[2]
        })
    return AllResponse.operate_successfully(data=response_data)


@work_investment_api.get('/tj/successRate/')  # 查询方案的人员成功率，品种成功率，时间成功率曲线
def strategy_success_rate(start: str = Query(None), end: str = Query(None),
                          variety: str = Query(None), author: int = Query(None)):
    start = datetime_utils.auth_datetime_string(start, f='%Y-%m-%d')
    end = datetime_utils.auth_datetime_string(end, f='%Y-%m-%d')
    start = start + ' 00:00:00' if start else '1970-01-01 00:00:00'
    end = end + ' 23:59:59' if end else '1970-01-01 00:00:00'
    author = author if author else '1970-01-01 00:00:00'
    query_sql = 'SELECT a.id,a.varieties,a.create_time,a.close_time,a.plan_profit,a.fund,a.creator,' \
                'b.admin_name ' \
                'FROM exchange_plan As a ' \
                'INNER JOIN ruizy_user As b ON a.creator=b.id ' \
                'WHERE a.is_delete=0 AND ' \
                'IF(%s="1970-01-01 00:00:00",TRUE,a.create_time>=%s) AND ' \
                'IF(%s="1970-01-01 00:00:00",TRUE,a.create_time<=%s) AND ' \
                'IF(%s="1970-01-01 00:00:00",TRUE,a.creator=%s);'
    query_prm = [start, start, end, end, author, author]
    db_conn = FAConnection()
    plan_list = db_conn.query(query_sql, query_prm)
    if variety:  # 过滤数据
        plan_list = list(filter(lambda x: variety in x['varieties'].split(','), plan_list))
    df = pd.DataFrame(plan_list)
    if df.empty or df[pd.notnull(df['close_time'])].empty:
        return AllResponse.server_no_content(msg='没有数据...')
    df['create_date'] = df['create_time'].apply(lambda x: x.strftime('%Y-%m-%d'))
    df['variety_en'] = df['varieties'].apply(lambda x: x.split(',')[0])
    df = df[pd.notnull(df['close_time'])]  # 去掉运行中的方案
    success_df = df[df['plan_profit'].gt(0)]
    # 1 以作者分组计算数量和比率
    author_df = df.groupby(by=['creator', 'admin_name'])['admin_name'].count()
    author_success_df = success_df.groupby(by=['creator', 'admin_name'])['admin_name'].count()
    author_success_dict = author_success_df.to_dict()
    author_data = []
    for k, c in author_df.to_dict().items():
        author_data.append({
            'author': k[1],
            'count': c,
            'success_count': author_success_dict.get(k, 0)
        })

    # 2 以品种分组计算数量和比率
    variety_df = df.groupby(by=['variety_en'])['variety_en'].count()
    variety_success_df = success_df.groupby(by=['variety_en'])['variety_en'].count()
    variety_success_dict = variety_success_df.to_dict()
    variety_data = []
    for k, c in variety_df.to_dict().items():
        variety_data.append({
            'variety_en': k,
            'variety_name': VARIETY_NAME.get(k, k),
            'count': c,
            'success_count': variety_success_dict.get(k, 0)
        })

    # 3 以日期分组计算数量和比率(累计成功率)
    date_series = df.groupby(by=['create_date'])['create_date'].count()
    date_success_series = success_df.groupby(by=['create_date'])['create_date'].count()
    # 计算数量和成功数的累计数
    date_df = pd.DataFrame({'create_date': date_series.index, 'count': date_series.values})
    date_df.sort_values(by='create_date')
    date_df['cum_count'] = date_df['count'].cumsum()
    date_success_df = pd.DataFrame({'create_date': date_success_series.index, 'count': date_success_series.values})
    date_success_df['cum_success_count'] = date_success_df['count'].cumsum()
    # 合并DataFrame
    date_mix_df = pd.merge(date_df, date_success_df, on='create_date', how='left')
    date_mix_df.fillna(method='ffill', inplace=True)
    date_mix_df.fillna(0, inplace=True)
    date_data = []
    for item in date_mix_df.to_dict(orient='records'):
        date_data.append({
            'create_date': item['create_date'],
            'count': item['cum_count'],
            'success_count': item['cum_success_count']
        })

    date_data.sort(key=lambda x: x['create_date'])
    # 返回的数据
    response_data = {
        'author':  author_data,
        'variety': variety_data,
        'daily': date_data
    }
    return AllResponse.operate_successfully(data=response_data)


@work_investment_api.get('/forCheck/')  # 按日期查询投资计划，检查使用
def query_investment_for_check(start: str = Query(...), end: str = Query(...)):
    start_time = datetime_utils.auth_datetime_string(start, f='%Y%m%d', rf='%Y-%m-%d 00:00:00')
    end_time = datetime_utils.auth_datetime_string(end, f='%Y%m%d', rf='%Y%m%d 23:59:59')
    if not all([start_time, end_time]):
        return AllResponse.validate_error(msg='查询日期格式错误！')
    db_conn = FAConnection()
    sql = "SELECT * FROM exchange_plan WHERE is_delete=0 AND create_time>=%s AND create_time<=%s AND is_delete=0;"
    records = db_conn.query(sql, param=[start_time, end_time])
    return AllResponse.operate_successfully(data=records)


@work_investment_api.put('/forCheck/')  # 检查后修改方案数据
def machine_modify_investment(body_data: dict = Body(...)):
    investment_id = body_data.get('id')
    if not investment_id:
        return AllResponse.validate_error(msg='参数有误')
    body_data['checker'] = '机器人'
    body_data['plan_profit'] = 0
    body_data = dbutils.exclude_columns(body_data, includes=['close_time', 'plan_profit', 'checker'])
    middle_sql = dbutils.create_sql(body_data)
    sql = 'UPDATE exchange_plan SET ' + middle_sql + 'WHERE id=%(plan_id)s LIMIT 1;'
    body_data['plan_id'] = investment_id
    db_conn = FAConnection()
    _, success = db_conn.execute(sql, param=body_data)
    return AllResponse.operate_successfully() if success else AllResponse.server_error()
